import pandas as pd
import sqlite3
import numpy as np

def group(data, key):
    groups = []
    for name,group in list(data.groupby(key)):
        group = {
            "name":name,
            "group":group
        }
        groups.append(group)
    return groups

class dataAnalysisUtil:
    # 连接数据库
    def __init__(self):
        self.conn = sqlite3.connect('../dataStorage/zhaopin.db')
        self.cursor = self.conn.cursor()
    
    # 读取数据，预处理
    def read(self, table_name):
        data = pd.read_sql(f"SELECT * FROM {table_name}", con=self.conn)
        data["MaxworkExperience"] = data["MaxworkExperience"].replace("0", np.nan)
        data["monthlyPay"] = data["monthlyPay"].replace("0", np.nan)
        data["monthlyPay"] = data["monthlyPay"].astype('float')
        data["monthlyPay"].fillna(data["monthlyPay"].sum()/data["monthlyPay"].count(), inplace=True)
        data["MinworkExperience"] = data["MinworkExperience"].astype('float') 
        data["corporateType"] = data["corporateType"].replace("", data["corporateType"].mode()[0])
        data["companySize"] = data["companySize"].replace("", data["companySize"].mode()[0])

        return data
    
    # 按学历分组分析数据
    def group_diploma(self, table_name):
        data = self.read(table_name)
        group_diplomas = group(data, 'diploma')
        msgs = [] 
        for i in group_diplomas:
            # Proportion：学历人群占比
            # avg_MinworkExperience：各学历平均最低工作经验
            # wep：各学历人群不限工作经验占该学历人群比例
            # avg_monthlyPay：各学历的平均工资：
            msg = {
                "name": i["name"],
                "Proportion": len(i['group'])/len(data),
                "avg_MinworkExperience": i['group']['MinworkExperience'].sum()/len(i['group']),
                "wep": 1-(i["group"]["MaxworkExperience"].count()/len(i["group"])),
                "avg_monthlyPay": i["group"]["monthlyPay"].sum()/len(i["group"]),
                "med_monthlyPay": i['group']['monthlyPay'].median()
            }
            msgs.append(msg)
        msgs.sort(key=lambda x:x['avg_monthlyPay'])
        return msgs

    # 按城市分组分析数据
    def group_city(self, table_name):
        data = self.read(table_name)
        group_citys = group(data, 'address')
        msgs = []
        for i in group_citys:
            msg = {
                "name": i["name"],
                "Proportion": len(i['group'])/len(data),
                "avg_monthlyPay": i["group"]["monthlyPay"].sum()/len(i["group"]),
                "med_monthlyPay": i["group"]["monthlyPay"].median()
            }
            msgs.append(msg)
        # msgs.sort(key=lambda x:x['Proportion'])
        msgs.sort(key=lambda x:x['Proportion'],reverse=True)
        msgs_sort = msgs[:10]
        # msgs.sort(key=lambda x:x['avg_monthlyPay'])
        # print(msgs_sort)
        msgs_sort.sort(key=lambda x:x['avg_monthlyPay'])
        return msgs_sort

    # 按公司类型分组
    def group_corporateType(self, table_name):
        data = self.read(table_name)
        group_diplomas = group(data, 'corporateType')
        msgs = [] 
        for i in group_diplomas:
            msg = {
                "name": i["name"],
                "Proportion": len(i['group'])/len(data),
                "avg_monthlyPay": i["group"]["monthlyPay"].sum()/len(i["group"]),
                "med_monthlyPay": i['group']['monthlyPay'].median()
            }
            msgs.append(msg)
        msgs.sort(key=lambda x:x['avg_monthlyPay'])
        return msgs
    
    # 按公司规模分组
    def group_companySize(self, table_name):
        data = self.read(table_name)
        group_diplomas = group(data, 'companySize')
        msgs = [] 
        for i in group_diplomas:
            msg = {
                "name": i["name"],
                "Proportion": len(i['group'])/len(data),
                "avg_monthlyPay": i["group"]["monthlyPay"].sum()/len(i["group"]),
                "med_monthlyPay": i['group']['monthlyPay'].median()
            }
            msgs.append(msg)
        msgs.sort(key=lambda x:x['avg_monthlyPay'])
        return msgs

    # 整体情况
    def all(self):
        # url_num = pd.read_csv("../reptile/url.csv")

        table_names = []
        sql = """
            select name,abb from dictionary
        """
        self.cursor.execute(sql)
        for row in self.cursor.fetchall():
            li_item = {
                "name": row[0],
                "abb": row[1]
            }
            table_names.append(li_item)

        msgs = []
        datas = []
        sum =0
        for table_name in table_names:
            sum += len(self.read(table_name["abb"]))
            data = {
                "name": table_name["name"],
                "data": self.read(table_name["abb"])
            }
            datas.append(data)
        
        for i in datas:
            msg = {
                "name":i["name"],
                "Proportion": len(i["data"])/sum,
                "avg_monthlyPay": i["data"]["monthlyPay"].sum()/len(i["data"]),
                "med_monthlyPay": i['data']['monthlyPay'].median()
            }
            msgs.append(msg)
        # msgs.sort(key=lambda x:x['avg_monthlyPay'],reverse=True)
        msgs.sort(key=lambda x:x['avg_monthlyPay'])
        return msgs

if __name__ == "__main__":
    data = dataAnalysisUtil().read("Java")  
    # print(data.info())  
    print("*"*50)
    analysis = dataAnalysisUtil()
    print(analysis.group_diploma("Hadoop"))
    print("*"*50)
    analysis = dataAnalysisUtil()
    print(analysis.group_city("Hadoop"))
    print("*"*50)
    analysis = dataAnalysisUtil()
    print(analysis.group_corporateType("Hadoop"))
    print("*"*50)
    analysis = dataAnalysisUtil()
    print(analysis.group_companySize("Hadoop"))
    print("*"*50)
    analysis = dataAnalysisUtil()
    print(analysis.all())
    

    